Module modAR

    Private isError As Boolean
    Private dtBP As DataTable
    Private dtcsCnt As DataTable
    Private dtINVHDr As DataTable
    Private dtCNHDr As DataTable
    Private dtItem As DataTable
    Private dtAC As DataTable

    ' Company Default Structure

    Public Structure ARHeader
        Public OwnerEntityCode As String
        Public DocumentType As String
        Public DocumentNo As String
        Public DocumentDate As Date
        Public CustomerCode As String
        Public ReferenceNumber As String
        Public ChitNo As String
        Public ClinicCode As String
        Public Description As String
        Public TaxCode As String
        Public PreTaxAmount As Double
        Public TaxAmount As Double
        Public TotalAmount As Double
        Public CurrencyCode As String
        Public ExchangeRate As Double
        Public PaymentMethod As String
        Public PaymentReceived As Double
        Public ImportToAP As String
        Public SupplierCode As String
    End Structure

    Public oARHeaderDef As ARHeader
    Private InputFolderPath As String = p_oCompDef.sInboxDir

    Public Function UploadAR(ByVal sSubFolder As String, ByRef sErrdesc As String) As Long

        'Event      :   UploadAR()
        'Purpose    :   For Checking Errors & updation of data in SAP from AR CSV Files
        'Author     :   Sri
        'Date       :   20 Nov 2013   

        Dim sFuncName As String = String.Empty
        Dim IsFileExist As Boolean
        Dim sFileType As String = String.Empty
        Dim oDvHdr As DataView = Nothing
        Dim oDvDtl As DataView = Nothing
        Dim sFileName As String = String.Empty
        Try
            sFuncName = "UploadAR"
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)

            Dim DirInfo As New System.IO.DirectoryInfo(p_oCompDef.sInboxDir & "\" & sSubFolder)
            Dim files() As System.IO.FileInfo

            files = DirInfo.GetFiles("*AR*.csv")

            For Each File As System.IO.FileInfo In files
                IsFileExist = True
                sFileType = Replace(File.Name, ".csv", "").Trim
                If Right(sFileType, 3) = "Dtl" Then
                    'Create_schema(p_oCompDef.sInboxDir, File.FullName)
                    oDvDtl = GetDataViewFromCSV(File.FullName)
                    'Del_schema(p_oCompDef.sInboxDir)
                Else
                    oDvHdr = GetDataViewFromCSV(File.FullName)
                    sFileName = File.Name
                    If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling readCSVFileAR_Header for " & File.FullName & " to check error in CSV file", sFuncName)
                    readCSVFileAR_Header(File.FullName, oDvHdr)
                    If isError = True Then
                        If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Wrong data in CSV file " & File.FullName & ".", sFuncName)
                        sErrdesc = "Invalid data in CSV file.::" & File.Name & " .Please refer to the Error log file for more details."
                        AddDataToTable(p_oDtError, File.Name, "Error", sErrdesc)
                    End If
                End If
            Next

            If isError = True Then
                For Each file As System.IO.FileInfo In files
                    FileMoveToArchive(file, file.FullName, RTN_ERROR)
                Next
                Throw New ArgumentException(sErrdesc)
            End If

            If IsFileExist = False Then
                If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("No AR CSV available for updation", sFuncName)
            Else
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling Function ProcessARFile()", sFuncName)
                If ProcessARFile(oDvHdr, oDvDtl, sSubFolder, sFileName, sErrdesc) <> RTN_SUCCESS Then
                    For Each file As System.IO.FileInfo In files
                        AddDataToTable(p_oDtError, file.Name, "Error", sErrdesc)
                        FileMoveToArchive(file, file.FullName, RTN_ERROR)
                    Next
                    Throw New ArgumentException(sErrdesc)
                Else
                    'Mvoe files
                    For Each file As System.IO.FileInfo In files
                        FileMoveToArchive(file, file.FullName, RTN_SUCCESS)
                    Next
                End If
            End If

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Function Completed Successfully.", sFuncName)

            UploadAR = RTN_SUCCESS

        Catch ex As Exception
            UploadAR = RTN_ERROR
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Error in Uplodiang AR file.", sFuncName)
            Call WriteToLogFile(ex.Message, sFuncName)
        End Try
    End Function

    Private Sub readCSVFileAR_Header(ByVal CurrFileToUpload As String, ByVal dv As DataView)

        'Event      :   readCSVFileAR_Header
        'Purpose    :   For reading of Business Partner CSV file
        'Author     :   Sri 
        'Date       :   24 NOV 2013 

        Dim sFuncName As String = "readCSVFileAR_Header"
        Dim sSQL As String = String.Empty
        Dim dt As DataTable
        Dim sErrDesc As String = String.Empty

        Try

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function...", sFuncName)

            sSQL = "SELECT ""Code"" FROM ""@AI_TB01_COMPANYDATA"""
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("EXECUTING SQL :" & sSQL, sFuncName)
            dt = ExecuteSQLQueryForDT(sSQL)


           
            For i As Integer = 0 To dv.Count - 1
                With oARHeaderDef
                    .OwnerEntityCode = dv(i)(0).ToString
                    .DocumentType = dv(i)(1).ToString
                    .DocumentNo = dv(i)(2).ToString
                    .DocumentDate = dv(i)(3)
                    .CustomerCode = dv(i)(4).ToString
                    .ReferenceNumber = dv(i)(5).ToString
                    .ChitNo = dv(i)(6).ToString
                    .ClinicCode = dv(i)(7).ToString
                    .Description = dv(i)(8).ToString
                    .TaxCode = dv(i)(9).ToString
                    .PreTaxAmount = dv(i)(10)
                    .TaxAmount = dv(i)(11)
                    .TotalAmount = dv(i)(12)
                    .CurrencyCode = dv(i)(13).ToString
                    .ExchangeRate = dv(i)(14)
                    .PaymentMethod = dv(i)(15).ToString
                    '.PaymentReceived = CDbl(dv(i)(16))
                    If Not IsDBNull(dv(i)(17)) Then
                        .ImportToAP = dv(i)(17).ToString
                    End If

                    If .OwnerEntityCode.Length = 0 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & CurrFileToUpload & ". OwnerEntityCode is mandatory", sFuncName)
                    End If

                    If .OwnerEntityCode.Length > 10 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & CurrFileToUpload & ". Owner entity Code Length is greater then 10", sFuncName)
                    End If

                    dt.DefaultView.RowFilter = "Code = '" & dv(i)(0).ToString & "'"
                    If dt.DefaultView.Count = 0 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & ". Owner enttiy code ::" & dv(i)(0).ToString & " does not exist in SAP [@AI_TB01_COMPANYDATA] table.", sFuncName)
                    End If

                    If .DocumentType.Length > 3 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & CurrFileToUpload & ". Document Type Length is not greater then 3", sFuncName)
                    End If

                    If .DocumentNo.Length > 30 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & CurrFileToUpload & ". Document No Length is not greater then 30", sFuncName)
                    End If

                    If .CustomerCode.Length > 10 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & CurrFileToUpload & ". Customer Code Length is not greater then 10", sFuncName)
                    End If


                    If .ReferenceNumber.Length > 30 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & CurrFileToUpload & ". Reference Number Length is not greater then 80", sFuncName)
                    End If
                    If .ChitNo.Length > 40 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & CurrFileToUpload & ". ChitNo Length is not greater then 40", sFuncName)
                    End If

                    If .ClinicCode.Length > 10 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & CurrFileToUpload & ". ClinicCode Length is not greater then 10", sFuncName)
                    End If

                    If .Description.Length > 255 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & CurrFileToUpload & ". Country Length is not greater then 255", sFuncName)
                    End If
                    If .TaxCode.Length > 10 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & CurrFileToUpload & ". TaxCode Length is not greater then 3", sFuncName)
                    End If

                    If .CurrencyCode.Length > 3 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & CurrFileToUpload & ". Currency Code Length is not greater then 3", sFuncName)
                    End If

                    If .PaymentMethod.Length > 50 Then
                        isError = True
                        Call WriteToLogFile("Check line no : " & CStr(i + 1) & " in " & CurrFileToUpload & ". PaymentMethod Length is not greater then 100", sFuncName)
                    End If

                End With
            Next


        Catch ex As Exception
            isError = True
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Error occured while reading content of  " & ex.Message, sFuncName)
            Call WriteToLogFile(ex.Message, sFuncName)
        End Try
    End Sub

    Private Function ProcessARFile(ByVal oDvHdr As DataView, _
                                   ByVal oDvDtl As DataView, _
                                   ByVal sSubFolder As String, _
                                   ByVal sFileName As String, _
                                   ByRef sErrdesc As String) As Long

        Dim sFuncName As String = "ProcessARFile"
        Dim oDtHdr As New DataTable
        Dim oDIComp() As SAPbobsCOM.Company = Nothing
        Dim oDt As DataTable
        Dim oCompanyAP As SAPbobsCOM.Company = Nothing
        Dim sSupplierCode As String = String.Empty
        Dim sSQL As String = String.Empty

        Try
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("getting distinct DBs in the file.", sFuncName)
            oDt = oDvHdr.Table.DefaultView.ToTable(True, "F1")

            ReDim oDIComp(oDt.Rows.Count)

            For j As Integer = 0 To oDt.Rows.Count - 1

                oDIComp(j) = New SAPbobsCOM.Company

                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling ConnectToTargetCompany()", sFuncName)
                If ConnectToTargetCompany(oDIComp(j), oDt.Rows(j).Item(0).ToString, sErrdesc) <> RTN_SUCCESS Then
                    Throw New ArgumentException(sErrdesc)
                End If

                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting transaction on company database " & oDIComp(j).CompanyDB, sFuncName)
                oDIComp(j).StartTransaction()

                Dim DBRows() As DataRow = oDvHdr.Table.Select("F1='" & oDt.Rows(j).Item(0).ToString & "'")

                For Each row As DataRow In DBRows

                    sSQL = "Select ""CardCode"" From OCRD WHERE ""CardType"" = 'C'"
                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("EXECUTING SQL :" & sSQL, sFuncName)
                    dtBP = ExecuteSQLQueryForDT(sSQL, oDIComp(j).CompanyDB)

                    sSQL = "select ""PrcCode"" from OPRC"
                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("EXECUTING SQL :" & sSQL, sFuncName)
                    dtcsCnt = ExecuteSQLQueryForDT(sSQL, oDIComp(j).CompanyDB)

                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Opening a datatable for Invoice Ref.Doc NO.", sFuncName)
                    sSQL = "SELECT ""U_AI_RefDocNum"" FROM OINV WHERE ""U_AI_RefDocNum"" IS NOT NULL"
                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("EXECUTING SQL :" & sSQL, sFuncName)
                    dtINVHDr = ExecuteSQLQueryForDT(sSQL, oDIComp(j).CompanyDB)

                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Opening a datatable for Invoice Ref.Doc NO.", sFuncName)
                    sSQL = "SELECT ""U_AI_RefDocNum"" FROM ORIN WHERE ""U_AI_RefDocNum"" IS NOT NULL"
                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("EXECUTING SQL :" & sSQL, sFuncName)
                    dtCNHDr = ExecuteSQLQueryForDT(sSQL, oDIComp(j).CompanyDB)

                    sSQL = "SELECT ""AcctCode"" FROM OACT"
                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("EXECUTING SQL :" & sSQL, sFuncName)
                    dtAC = ExecuteSQLQueryForDT(sSQL, oDIComp(j).CompanyDB)

                    If row.Item(1).ToString = "INV" Or row.Item(1).ToString = "SDN" Then
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling AddARInvoice for Cardcode ::" & row(4).ToString, sFuncName)
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling AddARInvoice for DocRefNo ::" & row(2).ToString, sFuncName)
                        If CDbl(row(10)) <> 0 Then
                            Dim iInvNo As Integer
                            If AddARInvoice_Service(oDIComp(j), row, oDvDtl, iInvNo, sSubFolder, sFileName, sErrdesc) <> RTN_SUCCESS Then
                                Throw New ArgumentException(sErrdesc)
                            End If
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling AddIncomingPayment for Cardcode:::" & row(4).ToString, sFuncName)
                            If Not IsDBNull(row.Item(16)) Then
                                If CDbl(row.Item(16)) > 0 Then
                                    If AddIncomingPayment(oDIComp(j), iInvNo, row, sErrdesc) <> RTN_SUCCESS Then
                                        Throw New ArgumentException(sErrdesc)
                                    End If
                                End If
                            End If

                        End If
                    ElseIf row.Item(1).ToString = "SCN" Then
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling AddCreditNote for Cardcode ::" & row(4).ToString, sFuncName)
                        If AddCreditNote_Service(oDIComp(j), row, oDvDtl, sSubFolder, sFileName, sErrdesc) <> RTN_SUCCESS Then
                            Throw New ArgumentException(sErrdesc)
                        End If
                    End If
                Next
            Next

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Commit all transaction..........", sFuncName)
            For lCounter As Integer = 0 To UBound(oDIComp)
                If Not oDIComp(lCounter) Is Nothing Then
                    If oDIComp(lCounter).Connected = True Then
                        If oDIComp(lCounter).InTransaction = True Then
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Commit transaction on company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                            oDIComp(lCounter).EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)
                        End If
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Disconnecting company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                        oDIComp(lCounter).Disconnect()
                        oDIComp(lCounter) = Nothing
                    End If
                End If
            Next

            If Not oCompanyAP Is Nothing Then
                If oCompanyAP.Connect = True Then
                    If oCompanyAP.InTransaction = True Then
                        oCompanyAP.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit)
                    End If
                    oCompanyAP.Disconnect()
                    oCompanyAP = Nothing
                End If
            End If

            ProcessARFile = RTN_SUCCESS
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Fucntion completed successfully.", sFuncName)

        Catch ex As Exception

            For lCounter As Integer = 0 To UBound(oDIComp)
                If Not oDIComp(lCounter) Is Nothing Then
                    If oDIComp(lCounter).Connected = True Then
                        If oDIComp(lCounter).InTransaction = True Then
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Rollback transaction on company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                            oDIComp(lCounter).EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                        End If
                        If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Disconnecting company database " & oDIComp(lCounter).CompanyDB, sFuncName)
                        oDIComp(lCounter).Disconnect()
                        oDIComp(lCounter) = Nothing
                    End If
                End If
            Next

            If Not oCompanyAP Is Nothing Then
                If oCompanyAP.Connect = True Then
                    If oCompanyAP.InTransaction = True Then
                        oCompanyAP.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack)
                    End If
                    oCompanyAP.Disconnect()
                    oCompanyAP = Nothing
                End If
            End If


            ProcessARFile = RTN_ERROR
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Error in Uploading AR File", sFuncName)
            Call WriteToLogFile(ex.Message, sFuncName)
        End Try
    End Function

    Private Function AddARInvoice(ByVal oCompany As SAPbobsCOM.Company, ByVal DtRow As DataRow, ByVal oDvDtl As DataView, ByRef iARInvNo As Integer, ByRef sErrDesc As String) As Long

        Dim sFuncName As String = "AddARInvoice"
        Dim sCardCode As String = String.Empty
        Dim oDoc As SAPbobsCOM.Documents
        Dim iCnt As Integer
        Dim lRetCode, lErrCode As Long
        Dim sBatchNo As String = String.Empty
        Dim sBatchPeriod As String = String.Empty
        Dim sRemarks As String = String.Empty

        Try
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Creating DIAPI ARI Object", sFuncName)
            oDoc = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInvoices)

            dtBP.DefaultView.RowFilter = "CardCode = '" & DtRow.Item(4).ToString & "'"
            If dtBP.DefaultView.Count = 0 Then
                sErrDesc = "Cardcode ::" & DtRow.Item(4).ToString & " provided does not exist in SAP."
                Call WriteToLogFile(sErrDesc, sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If

            dtINVHDr.DefaultView.RowFilter = "U_AI_RefDocNum = '" & DtRow.Item(2).ToString & "'"
            If dtINVHDr.DefaultView.Count <> 0 Then
                sErrDesc = "Document NO ::" & DtRow.Item(2).ToString & " already exist in SAP."
                Call WriteToLogFile(sErrDesc, sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If

            oDoc.CardCode = DtRow.Item(4).ToString
            oDoc.DocDate = DtRow.Item(3)
            oDoc.DocDueDate = DtRow.Item(3)
            oDoc.TaxDate = DtRow.Item(3)
            oDoc.NumAtCard = DtRow.Item(5).ToString
            oDoc.Comments = DtRow.Item(8).ToString
            oDoc.UserFields.Fields.Item("U_AI_ChitNo").Value = DtRow.Item(6).ToString

            If CDbl(DtRow.Item(14)) > 0 Then
                oDoc.DocRate = CDbl(DtRow.Item(14))
            End If
            oDoc.UserFields.Fields.Item("U_AI_RefDocNum").Value = DtRow.Item(2).ToString

            Dim DtlRows() As DataRow = oDvDtl.Table.Select("F1='" & DtRow.Item(2).ToString & "'")

            For Each LineRow As DataRow In DtlRows
                iCnt += 1
                If iCnt > 1 Then
                    oDoc.Lines.Add()
                End If

                dtItem.DefaultView.RowFilter = "ItemCode = '" & LineRow(1).ToString & "'"
                If dtItem.DefaultView.Count = 0 Then
                    sErrDesc = "Item code::" & LineRow(1).ToString & "  provided does not exist in SAP."
                    Call WriteToLogFile(sErrDesc, sFuncName)
                    Throw New ArgumentException(sErrDesc)
                End If

                dtAC.DefaultView.RowFilter = "AcctCode = '" & LineRow(2).ToString & "'"
                If dtAC.DefaultView.Count = 0 Then
                    sErrDesc = "AcctCode::" & LineRow(2).ToString & "  provided does not exist in SAP."
                    Call WriteToLogFile(sErrDesc, sFuncName)
                    Throw New ArgumentException(sErrDesc)
                End If

                dtcsCnt.DefaultView.RowFilter = "PrcCode = '" & DtRow(7).ToString & "'"
                If dtcsCnt.DefaultView.Count = 0 Then
                    sErrDesc = "Clinic code::" & DtRow(7).ToString & "  provided does not exist in SAP."
                    Call WriteToLogFile(sErrDesc, sFuncName)
                    Throw New ArgumentException(sErrDesc)
                End If

                oDoc.Lines.ItemCode = LineRow(1).ToString
                oDoc.Lines.Quantity = LineRow(4)
                oDoc.Lines.UnitPrice = CDbl(LineRow(5))
                oDoc.Lines.AccountCode = LineRow(2).ToString
                oDoc.PaymentMethod = String.Empty

                If CDbl(DtRow.Item(11)) > 0 Then
                    oDoc.Lines.VatGroup = "SO"
                Else
                    oDoc.Lines.VatGroup = "ZO"
                End If
                If Not DtRow(7).ToString = String.Empty Then
                    oDoc.Lines.COGSCostingCode2 = DtRow(7).ToString
                End If
            Next

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding ARI.", sFuncName)
            lRetCode = oDoc.Add
            If lRetCode <> 0 Then
                oCompany.GetLastError(lErrCode, sErrDesc)
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding ARI failed.", sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If

            oCompany.GetNewObjectCode(iARInvNo)

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Fucntion Completed Succesfully.", sFuncName)
            AddARInvoice = RTN_SUCCESS

        Catch ex As Exception
            AddARInvoice = RTN_ERROR
            sErrDesc = ex.Message
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile("Function completed with ERROR:" & sErrDesc, sFuncName)
        End Try
    End Function

    Private Function AddIncomingPayment(ByVal oCompany As SAPbobsCOM.Company, ByVal iInvNo As Integer, ByVal DtRow As DataRow, ByRef sErrdesc As String) As Long

        Dim sFuncName As String = "AddIncomingPayment"
        Dim oPayment As SAPbobsCOM.IPayments
        Dim lRetCode, lErrCode As Long

        Try
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function..", sFuncName)

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Retrieving Incoming Payment Object", sFuncName)
            oPayment = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oIncomingPayments)
            oPayment.DocDate = DtRow.Item(3)

            oPayment.Invoices.SetCurrentLine(0)
            oPayment.Invoices.DocEntry = iInvNo
            oPayment.Invoices.InvoiceType = SAPbobsCOM.BoRcptInvTypes.it_Invoice
            oPayment.Invoices.SumApplied = CDbl(DtRow.Item(16))
            oPayment.Invoices.Add()

            oPayment.CardCode = DtRow.Item(4).ToString
            oPayment.CashSum = DtRow.Item(16)
            oPayment.CashAccount = "1-11300-00"
            oPayment.Remarks = DtRow.Item(15).ToString

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding Incoming Payment Document", sFuncName)
            lRetCode = oPayment.Add
            If lRetCode <> 0 Then
                oCompany.GetLastError(lErrCode, sErrdesc)
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding Incoming paymnet failed.", sFuncName)
                Throw New ArgumentException(sErrdesc)
            End If

            AddIncomingPayment = RTN_SUCCESS
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Fucntion completed successfully.", sFuncName)

        Catch ex As Exception
            AddIncomingPayment = RTN_ERROR
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Error in Procedure ProcForBP", sFuncName)
            Call WriteToLogFile(ex.Message, sFuncName)
        End Try
    End Function

    Private Function AddCreditNote(ByVal oCompany As SAPbobsCOM.Company, ByVal DtRow As DataRow, ByVal oDvDtl As DataView, ByRef sErrDesc As String) As Long

        Dim sFuncName As String = "AddCreditNote"
        Dim sCardCode As String = String.Empty
        Dim oDoc As SAPbobsCOM.Documents
        Dim iCnt As Integer
        Dim lRetCode, lErrCode As Long
        Dim sBatchNo As String = String.Empty
        Dim sBatchPeriod As String = String.Empty
        Dim sRemarks As String = String.Empty

        Try
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Creating DIAPI ARCN Object", sFuncName)
            oDoc = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oCreditNotes)

            dtBP.DefaultView.RowFilter = "CardCode = '" & DtRow.Item(4).ToString & "'"
            If dtBP.DefaultView.Count = 0 Then
                sErrDesc = "Cardcode ::" & DtRow.Item(4).ToString & " provided does not exist in SAP."
                Call WriteToLogFile(sErrDesc, sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If

            dtCNHDr.DefaultView.RowFilter = "U_AI_RefDocNum = '" & DtRow.Item(2).ToString & "'"
            If dtCNHDr.DefaultView.Count <> 0 Then
                sErrDesc = "Document NO ::" & DtRow.Item(2).ToString & " provided does not exist in SAP."
                Call WriteToLogFile(sErrDesc, sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If


            oDoc.CardCode = DtRow.Item(4).ToString
            oDoc.DocDate = DtRow.Item(3)
            oDoc.DocDueDate = DtRow.Item(3)
            oDoc.TaxDate = DtRow.Item(3)
            oDoc.NumAtCard = DtRow.Item(5).ToString
            oDoc.Comments = DtRow.Item(8).ToString
            oDoc.UserFields.Fields.Item("U_AI_ChitNo").Value = DtRow.Item(6).ToString
            oDoc.UserFields.Fields.Item("U_AI_RefDocNum").Value = DtRow.Item(2).ToString

            Dim DtlRows() As DataRow = oDvDtl.Table.Select("F1='" & DtRow.Item(2).ToString & "'")

            For Each LineRow As DataRow In DtlRows
                iCnt += 1
                If iCnt > 1 Then
                    oDoc.Lines.Add()
                End If

                dtItem.DefaultView.RowFilter = "ItemCode = '" & LineRow(1).ToString & "'"
                If dtItem.DefaultView.Count = 0 Then
                    sErrDesc = "Item code::" & LineRow(1).ToString & "  provided does not exist in SAP."
                    Call WriteToLogFile(sErrDesc, sFuncName)
                    Throw New ArgumentException(sErrDesc)
                End If

                dtAC.DefaultView.RowFilter = "AcctCode = '" & LineRow(2).ToString & "'"
                If dtAC.DefaultView.Count = 0 Then
                    sErrDesc = "AcctCode::" & LineRow(2).ToString & "  provided does not exist in SAP."
                    Call WriteToLogFile(sErrDesc, sFuncName)
                    Throw New ArgumentException(sErrDesc)
                End If

                'dtcsCnt.DefaultView.RowFilter = "PrcCode = '" & DtRow(7).ToString & "'"
                'If dtcsCnt.DefaultView.Count = 0 Then
                '    sErrDesc = "Clinic code::" & DtRow(9).ToString & "  provided does not exist in SAP."
                '    Call WriteToLogFile(sErrDesc, sFuncName)
                '    Throw New ArgumentException(sErrDesc)
                'End If

                oDoc.Lines.ItemCode = LineRow(1).ToString
                oDoc.Lines.Quantity = LineRow(4)
                oDoc.Lines.UnitPrice = CDbl(LineRow(5))
                oDoc.Lines.AccountCode = LineRow(2).ToString

                If CDbl(DtRow.Item(11)) > 0 Then
                    oDoc.Lines.VatGroup = "SO"
                Else
                    oDoc.Lines.VatGroup = "ZO"
                End If
                If Not DtRow(7).ToString = String.Empty Then
                    'oDoc.Lines.COGSCostingCode = DtRow(9).ToString
                End If
            Next

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding ARI.", sFuncName)
            lRetCode = oDoc.Add
            If lRetCode <> 0 Then
                oCompany.GetLastError(lErrCode, sErrDesc)
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding ARI failed.", sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Fucntion Completed Succesfully.", sFuncName)
            AddCreditNote = RTN_SUCCESS

        Catch ex As Exception
            AddCreditNote = RTN_ERROR
            sErrDesc = ex.Message
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile("Function completed with ERROR:" & sErrDesc, sFuncName)
        End Try
    End Function

    Private Function AddARInvoice_Service(ByVal oCompany As SAPbobsCOM.Company, _
                                          ByVal DtRow As DataRow, _
                                          ByVal oDvDtl As DataView, _
                                          ByRef iARInvNo As Integer, _
                                          ByVal sSubFolder As String, _
                                          ByVal sFileName As String, _
                                          ByRef sErrDesc As String) As Long

        Dim sFuncName As String = "AddARInvoice_Service"
        Dim sCardCode As String = String.Empty
        Dim oDoc As SAPbobsCOM.Documents
        Dim iCnt As Integer
        Dim lRetCode, lErrCode As Long
        Dim sBatchNo As String = String.Empty
        Dim sBatchPeriod As String = String.Empty
        Dim sRemarks As String = String.Empty
        Dim dCOGSAmt As Double = 0
        Dim sCOGSGLAccount As String = String.Empty
        Dim sInvGLAccount As String = String.Empty
        Dim sCostCenter As String = String.Empty

        Try
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Creating DIAPI ARI Object", sFuncName)
            oDoc = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInvoices)

            oDoc.DocType = SAPbobsCOM.BoDocumentTypes.dDocument_Service


            dtBP.DefaultView.RowFilter = "CardCode = '" & DtRow.Item(4).ToString & "'"
            If dtBP.DefaultView.Count = 0 Then
                sErrDesc = "Cardcode ::" & DtRow.Item(4).ToString & " provided does not exist in SAP."
                Call WriteToLogFile(sErrDesc, sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If

            dtINVHDr.DefaultView.RowFilter = "U_AI_RefDocNum = '" & DtRow.Item(2).ToString.Trim & "'"
            If dtINVHDr.DefaultView.Count <> 0 Then
                sErrDesc = "Document NO ::" & DtRow.Item(2).ToString & " already exist in SAP."
                Call WriteToLogFile(sErrDesc, sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If

            oDoc.CardCode = DtRow.Item(4).ToString
            oDoc.DocDate = DtRow.Item(3)
            oDoc.DocDueDate = DtRow.Item(3)
            oDoc.TaxDate = DtRow.Item(3)
            oDoc.NumAtCard = DtRow.Item(5).ToString
            oDoc.Comments = DtRow.Item(8).ToString
            oDoc.UserFields.Fields.Item("U_AI_ChitNo").Value = DtRow.Item(6).ToString
            oDoc.PaymentMethod = String.Empty
            oDoc.UserFields.Fields.Item("U_AI_APARUploadName").Value = sFileName

            If CDbl(DtRow.Item(14)) > 0 Then
                oDoc.DocRate = CDbl(DtRow.Item(14))
            End If
            oDoc.UserFields.Fields.Item("U_AI_RefDocNum").Value = DtRow.Item(2).ToString.Trim
            oDoc.NumAtCard = DtRow.Item(2).ToString

            If DtRow.Item(17).ToString = "Y" Then
                oDoc.UserFields.Fields.Item("U_AI_MARIICCode").Value = DtRow.Item(2).ToString
                Dim k As Integer
                Dim sCode As String = DtRow.Item(4).ToString
                k = InStr(sCode, "-")
                If k <> 0 Then sCode = Microsoft.VisualBasic.Left(sCode, k - 1).Trim
                sCode = Right(sCode, sCode.Length - 2)
                oDoc.UserFields.Fields.Item("U_AI_MARIICCompany").Value = sCode
            End If

            Dim DtlRows() As DataRow = oDvDtl.Table.Select("F1='" & DtRow.Item(2).ToString & "'")

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Detail Items Row Count for Doc Ref.No " & DtRow(2).ToString & "::" & DtlRows.Length, sFuncName)

            For Each LineRow As DataRow In DtlRows
                iCnt += 1
                If iCnt > 1 Then
                    oDoc.Lines.Add()
                End If

                dtAC.DefaultView.RowFilter = "AcctCode = '" & LineRow(2).ToString & "'"
                If dtAC.DefaultView.Count = 0 Then
                    sErrDesc = "AcctCode::" & LineRow(2).ToString & "  provided does not exist in SAP."
                    Call WriteToLogFile(sErrDesc, sFuncName)
                    Throw New ArgumentException(sErrDesc)
                End If

                dtcsCnt.DefaultView.RowFilter = "PrcCode = '" & DtRow(7).ToString & "'"
                If dtcsCnt.DefaultView.Count = 0 Then
                    sErrDesc = "Clinic code::" & DtRow(7).ToString & "  provided does not exist in SAP."
                    Call WriteToLogFile(sErrDesc, sFuncName)
                    Throw New ArgumentException(sErrDesc)
                End If

                oDoc.Lines.AccountCode = LineRow(2).ToString
                oDoc.Lines.ItemDescription = LineRow(3).ToString
                oDoc.Lines.LineTotal = CDbl(LineRow(6))

                If CDbl(DtRow.Item(11)) > 0 Then
                    oDoc.Lines.VatGroup = "SO"
                Else
                    oDoc.Lines.VatGroup = "ZO"
                End If

                If Not DtRow(7).ToString = String.Empty Then
                    oDoc.Lines.CostingCode2 = DtRow(7).ToString
                    oDoc.Lines.COGSCostingCode2 = DtRow(7).ToString
                End If

                If sSubFolder = "AMPHARM" Then
                    dCOGSAmt = dCOGSAmt + CDbl(LineRow(12))
                    sCOGSGLAccount = LineRow(10).ToString
                    sInvGLAccount = LineRow(11).ToString
                    sCostCenter = DtRow(7).ToString
                End If

            Next

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding AR Invoice.", sFuncName)
            lRetCode = oDoc.Add
            If lRetCode <> 0 Then
                oCompany.GetLastError(lErrCode, sErrDesc)
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding AR Invoice failed.", sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If

            oCompany.GetNewObjectCode(iARInvNo)
            'Create JE
            If sSubFolder = "AMPHARM" Then
                If dCOGSAmt > 0 Then
                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling CreateJE()", sFuncName)
                    If CreateJE(oCompany, DtRow, sCOGSGLAccount, sInvGLAccount, dCOGSAmt, sCostCenter, sErrDesc) <> RTN_SUCCESS Then
                        Throw New ArgumentException(sErrDesc)
                    End If
                End If
            End If
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Fucntion Completed Succesfully.", sFuncName)
            AddARInvoice_Service = RTN_SUCCESS

        Catch ex As Exception
            AddARInvoice_Service = RTN_ERROR
            sErrDesc = ex.Message
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile("Function completed with ERROR:" & sErrDesc, sFuncName)
        End Try
    End Function

    Private Function AddCreditNote_Service(ByVal oCompany As SAPbobsCOM.Company, _
                                            ByVal DtRow As DataRow, _
                                            ByVal oDvDtl As DataView, _
                                            ByVal sSubFolder As String, _
                                            ByVal sFileName As String, _
                                            ByRef sErrDesc As String) As Long

        Dim sFuncName As String = "AddCreditNote_Service"
        Dim sCardCode As String = String.Empty
        Dim oDoc As SAPbobsCOM.Documents
        Dim iCnt As Integer
        Dim lRetCode, lErrCode As Long
        Dim sBatchNo As String = String.Empty
        Dim sBatchPeriod As String = String.Empty
        Dim sRemarks As String = String.Empty
        Dim dCOGSAmt As Double = 0
        Dim sCOGSGLAccount As String = String.Empty
        Dim sInvGLAccount As String = String.Empty
        Dim sCostCenter As String = String.Empty

        Try
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Creating DIAPI ARCN Object", sFuncName)
            oDoc = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oCreditNotes)

            oDoc.DocType = SAPbobsCOM.BoDocumentTypes.dDocument_Service

            dtBP.DefaultView.RowFilter = "CardCode = '" & DtRow.Item(4).ToString & "'"
            If dtBP.DefaultView.Count = 0 Then
                sErrDesc = "Cardcode ::" & DtRow.Item(4).ToString & " provided does not exist in SAP."
                Call WriteToLogFile(sErrDesc, sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If

            dtCNHDr.DefaultView.RowFilter = "U_AI_RefDocNum = '" & DtRow.Item(2).ToString & "'"
            If dtCNHDr.DefaultView.Count <> 0 Then
                sErrDesc = "Document NO ::" & DtRow.Item(2).ToString & " provided does not exist in SAP."
                Call WriteToLogFile(sErrDesc, sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If


            oDoc.CardCode = DtRow.Item(4).ToString
            oDoc.DocDate = DtRow.Item(3)
            oDoc.DocDueDate = DtRow.Item(3)
            oDoc.TaxDate = DtRow.Item(3)
            oDoc.NumAtCard = DtRow.Item(5).ToString
            oDoc.Comments = DtRow.Item(8).ToString
            oDoc.UserFields.Fields.Item("U_AI_ChitNo").Value = DtRow.Item(6).ToString
            oDoc.UserFields.Fields.Item("U_AI_RefDocNum").Value = DtRow.Item(2).ToString
            oDoc.NumAtCard = DtRow.Item(2).ToString
            oDoc.UserFields.Fields.Item("U_AI_APARUploadName").Value = sFileName

            Dim DtlRows() As DataRow = oDvDtl.Table.Select("F1='" & DtRow.Item(2).ToString & "'")

            For Each LineRow As DataRow In DtlRows
                iCnt += 1
                If iCnt > 1 Then
                    oDoc.Lines.Add()
                End If

                dtAC.DefaultView.RowFilter = "AcctCode = '" & LineRow(2).ToString & "'"
                If dtAC.DefaultView.Count = 0 Then
                    sErrDesc = "AcctCode::" & LineRow(2).ToString & "  provided does not exist in SAP."
                    Call WriteToLogFile(sErrDesc, sFuncName)
                    Throw New ArgumentException(sErrDesc)
                End If

                oDoc.Lines.AccountCode = LineRow(2).ToString
                oDoc.Lines.ItemDescription = LineRow(3).ToString
                oDoc.Lines.LineTotal = CDbl(LineRow(6)) * -1

                If (CDbl(DtRow.Item(11)) * -1) > 0 Then
                    oDoc.Lines.VatGroup = "SO"
                Else
                    oDoc.Lines.VatGroup = "ZO"
                End If

                If Not DtRow(7).ToString = String.Empty Then
                    oDoc.Lines.CostingCode2 = DtRow(7).ToString
                    oDoc.Lines.COGSCostingCode2 = DtRow(7).ToString
                End If

                If sSubFolder = "AMPHARM" Then
                    dCOGSAmt = dCOGSAmt + CDbl(LineRow(12))
                    sCOGSGLAccount = LineRow(10).ToString
                    sInvGLAccount = LineRow(11).ToString
                    sCostCenter = DtRow(7).ToString
                End If

            Next

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding AR Credit Note.", sFuncName)
            lRetCode = oDoc.Add
            If lRetCode <> 0 Then
                oCompany.GetLastError(lErrCode, sErrDesc)
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding AR Credit Note failed.", sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If

            'Create JE
            If sSubFolder = "AMPHARM" Then
                If dCOGSAmt > 0 Then
                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling CreateJE()", sFuncName)
                    If CreateJE(oCompany, DtRow, sCOGSGLAccount, sInvGLAccount, dCOGSAmt, sCostCenter, sErrDesc) <> RTN_SUCCESS Then
                        Throw New ArgumentException(sErrDesc)
                    End If
                End If
            End If

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Fucntion Completed Succesfully.", sFuncName)
            AddCreditNote_Service = RTN_SUCCESS

        Catch ex As Exception
            AddCreditNote_Service = RTN_ERROR
            sErrDesc = ex.Message
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile("Function completed with ERROR:" & sErrDesc, sFuncName)
        End Try
    End Function

    Private Function CreateJE(ByVal oCompany As SAPbobsCOM.Company, _
                                ByVal DtRow As DataRow, _
                                ByVal sCOGSAccount As String, _
                                ByVal sInvAccount As String, _
                                ByVal dCOGSAmt As Double, _
                                ByVal sCostCenter As String, _
                                ByRef sErrDesc As String) As Long
        ' *********************************************************************************************
        '   Function   :   CreateJE
        '   Purpose    :   To Create Journel Entry
        '   Parameters :   ByVal oCompany As SAPbobsCOM.Company
        '                   oCompany= DIAPI Company Details
        '                  ByRef sErrDesc As String
        '                   sErrDesc=Error Description to be returned to calling function
        '   Return     :   0 - FAILURE
        '                  1 - SUCCESS
        '   Author     :   Sri
        '   Date       :   01/09/2014
        '   Change     :
        ' *********************************************************************************************

        Dim oJE As SAPbobsCOM.JournalEntries
        Dim sFuncName As String = String.Empty
        Dim lRetVal As Long = 0
        Dim lErrCode As Long = 0

        Try
            sFuncName = "CreateJE()"
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Retrieving Journel Entry Object", sFuncName)
            oJE = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oJournalEntries)

            oJE.DueDate = DtRow.Item(3)
            oJE.TaxDate = DtRow.Item(3)
            oJE.ReferenceDate = DtRow.Item(3)

            oJE.Memo = "Cost of Sales"
            oJE.Reference = DtRow.Item(2).ToString

            'Debit
            oJE.Lines.AccountCode = sCOGSAccount
            oJE.Lines.Debit = dCOGSAmt
            oJE.Lines.ShortName = sCOGSAccount
            oJE.Lines.CostingCode2 = sCostCenter
            oJE.Lines.Add()

            'Credit
            oJE.Lines.AccountCode = sInvAccount
            oJE.Lines.Credit = dCOGSAmt
            oJE.Lines.ShortName = sInvAccount
            oJE.Lines.CostingCode2 = sCostCenter

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding Journel Entry", sFuncName)
            lRetVal = oJE.Add
            If lRetVal <> 0 Then
                oCompany.GetLastError(lErrCode, sErrDesc)
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Failed to create JE", sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Completed with SUCCESS", sFuncName)
            CreateJE = RTN_SUCCESS
        Catch Ex As Exception
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Completed with ERROR", sFuncName)
            WriteToLogFile(Ex.Message, sFuncName)
            CreateJE = RTN_ERROR
        End Try
    End Function



End Module
